package dao.Impl;

import Util.DataUtil;
import bean.TInsect;
import dao.insectDao;

import javax.xml.crypto.Data;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class insectDaoimpl implements insectDao {

    @Override
    public List<TInsect> insectlist() {
        List<TInsect> list = new ArrayList<>();
        Connection conet = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select * from t_insect";
        try {
            conet = DataUtil.getConn();
            ps = conet.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()) {
                TInsect insect = new TInsect();
                insect.setPkId(rs.getInt("pk_id"));
                insect.setInsect(rs.getString("insect"));
                insect.setHost(rs.getString("host"));
                insect.setDanger(rs.getString("danger"));
                list.add(insect);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    @Override
    public List<TInsect> ListByPage(int pageNo, int pageSize, String insectname) {
        List<TInsect> list = new ArrayList<>();
        Connection conet = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conet = DataUtil.getConn();
            String sql = "select * from t_insect where 1=1";
            if (insectname != null && !insectname.equalsIgnoreCase("")) {
                sql += " and insect like '%" + insectname + "%'";
                System.out.println(sql);
            }
            sql += " limit " + (pageNo - 1) * pageSize + "," + pageSize;
            ps = conet.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                TInsect Ti = new TInsect();
                Ti.setDanger(rs.getString("danger"));
                Ti.setInsect(rs.getString("insect"));
                Ti.setPkId(rs.getInt("pk_id"));
                Ti.setHost(rs.getString("host"));
                list.add(Ti);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DataUtil.close(rs, ps, conet);
        }

        return list;
    }

    @Override
    public boolean add(TInsect insect) {
        int count = 0;
        Connection conet = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        boolean flag = false;
        try {
            conet= DataUtil.getConn();
            String sql = "insert into t_insect(insect,host,danger) value(?,?,?)";
            ps = conet.prepareStatement(sql);
            ps.setString(1,insect.getInsect());
            ps.setString(2,insect.getHost());
            ps.setString(3,insect.getDanger());
            count = ps.executeUpdate();
            if(count!=0){
                flag=true;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DataUtil.close(rs,ps,conet);
        }
        return flag;
    }

    @Override
    public boolean delete(TInsect insect) {
      return false;
    }



    @Override
    public TInsect find(TInsect insect) {
        return null;
    }

    @Override
    public int getListCount(String insect) {
        int count = 0;
        Connection conet = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conet= DataUtil.getConn();
            System.out.println("insect = " + insect);
            String sql = "select count(0) from t_insect where 1=1";

            if(insect!=null&&insect.equalsIgnoreCase("")){
                sql+=" and insect like '%"+insect+"%'"   ;
            }

            ps = conet.prepareStatement(sql);
            rs = ps.executeQuery();
            if(rs.next()){
                count = rs.getInt(1);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DataUtil.close(rs,ps,conet);
        }
        System.out.println("count = " + count);
        return count;
    }
}
